﻿using System;
using System.Collections.Generic;
using System.Text;

namespace DBDataAccessLib
{
    public abstract class SqlProvider
    {
        public static readonly string prefix = DBHelperFactory.Prefix;
        public static readonly string SelectUserInfo = "select * from Account where UserName=" + prefix + "userName and UserPassword=" + prefix + "password and System='ALP'";
        public static readonly string SelectAllUsers = "select * from Account where System='ALP'";
        public static readonly string UpdateUser = "update Account set UserPassword=" + prefix + "Password,UserFunction=" + prefix + "function where UserName=" + prefix + "userName and System='ALP'";
        public static readonly string AddUser = "insert into Account (UserName,UserPassword,UserFunction,System) values (" + prefix + "userName," + prefix + "password," + prefix + "function,'ALP')";
        public static readonly string DeleteUser = "delete from Account where UserName=" + prefix + "userName and System='ALP'";

        public static readonly string SelectAllDBNames = "select * from sys.databases";

        public static readonly string GetDBTime = "select to_char(sysdate,'yyyy.MM') from dual";

        public static readonly string SelectAllWCNOLines = "select distinct WCNO from MEPS_STATION_NAME";
        public static readonly string SelectStationNamesByWCNO = "select STATION_NAME from MEPS_STATION_NAME where WCNO=" + prefix + "wcno";
        public static readonly string AddStationName = "insert into MEPS_STATION_NAME(WCNO,STATION_NAME) values(" + prefix + "wcno," + prefix + "stationName)";
        public static readonly string AddWCNOLine = "insert into MEPS_STATION_NAME(WCNO) values(" + prefix + "wcno)";
        public static readonly string DeleteWCNOLine = "delete from MEPS_STATION_NAME where WCNO=" + prefix + "wcno";
        public static readonly string DeleteStationName = "delete from MEPS_STATION_NAME where STATION_NAME=" + prefix + "stationName and WCNO=" + prefix + "wcno";

        public static readonly string AddMachineInfo = "insert into MEPS_INFORMATION (WCNO,STATION_NAME,MODEL,SEBANGO,VALID,ESSENTIALS,CYCLE_TIME,SET_ID,SET_DATE) " + "values(" + prefix + "wcno," + prefix + "stationName," + prefix + "model," + prefix + "sebango," + prefix + "valid," + prefix + "essentials," + prefix + "cycleTime," + prefix + "setUser," + prefix + "setDate)";
        public static readonly string SelectMachineInfo = "select * from MEPS_INFORMATION where WCNO=" + prefix + "wcno and STATION_NAME=" + prefix + "stationName and MODEL=" + prefix + "model";
        public static readonly string SelectMachineInfoByConditions = "select * from MEPS_INFORMATION where WCNO like " + prefix + "wcno and STATION_NAME like " + prefix + "stationName and " + "MODEL like " + prefix + "model and SEBANGO like " + prefix + "sebango and SET_ID like " + prefix + "setId";
        public static readonly string UpdateMachineInfo = "update MEPS_INFORMATION set WCNO=" + prefix + "wcno,STATION_NAME=" + prefix + "stationName," + "MODEL=" + prefix + "model,SEBANGO=" + prefix + "sebango,ESSENTIALS=" + prefix + "essentials,CYCLE_TIME=" + prefix + "cycleTime," + "SET_ID=" + prefix + "setUser,SET_DATE=" + prefix + "setDate where WCNO=" + prefix + "wcno and STATION_NAME=" + prefix + "stationName and " + "MODEL=" + prefix + "model";
        public static readonly string DeleteMachineInfo = "delete from MEPS_INFORMATION where WCNO=" + prefix + "wcno and STATION_NAME=" + prefix + "stationName and " + "MODEL=" + prefix + "model";
        public static readonly string SelectRecordByKeyAndValid = "select * from MEPS_INFORMATION where WCNO=" + prefix + "wcno and STATION_NAME=" + prefix + "stationName and MODEL=" + prefix + "model and Valid=" + prefix + "valid";
        public static readonly string UpdateValidByKeyAndValid = "update MEPS_INFORMATION set Valid=" + prefix + "setValid,APPLY_ID=" + prefix + "applyUser,APPLY_DATE=" + prefix + "applyDate where WCNO=" + prefix + "wcno and STATION_NAME=" + prefix + "stationName and MODEL=" + prefix + "model and Valid=" + prefix + "findValid";
        public static readonly string UpdateValidByKeyAndValidWithoutApplyID = "update MEPS_INFORMATION set Valid=" + prefix + "setValid where WCNO=" + prefix + "wcno and STATION_NAME=" + prefix + "stationName and MODEL=" + prefix + "model and Valid=" + prefix + "findValid";

        public static readonly string SelectedRecordsByJIBU = "select * from LINE_NAME where JIBU=" + prefix + "jibu";
        public static readonly string SelectedGSVXRecords = "select EG_PRODUCT_CODE,SEBANGO from GSVX_GM_MV";
    }
}
